//
//  DBMan.m
//  microtv
//
//  Created by zhang jun on 12-3-23.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import "DBMan.h"
#import "MsgInfo.h"


static DBMan* dbm = nil;

@implementation DBMan

@synthesize fmdb;


+(DBMan*)shareDB{
    
    @synchronized(self){
        
        if ( dbm == nil) {
            dbm = [[DBMan alloc] init];
            [dbm createDB];
        }
    }
    
    return dbm;
}




// 返回数据库的绝对路径
- (NSString *) databasePath
{
    NSString *path = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents"];
    
    /* 取得Documents目录 */
    /* PathComponent 就是/  */
    NSLog(@"DB_path is %@", path);
    return [path stringByAppendingPathComponent:DBNAME];
}


//创建数据库
- (void) createDB{
    
    /* 1. 取得数据库名字 */
    NSString *name = [self databasePath];
    /* 2. 打开数据库，如果数据库不存在 就新创建一个数据库 */
    
    self.fmdb = [FMDatabase databaseWithPath:name];
    if (![self.fmdb open]) {
        //NSLog(@"Could not open db.");
        return ;
    }
}



//初始化数据库所有的表
-(void)initAllTableOfDB{
    
    //房贷计算历史表
    NSString* table_loan_info = @"CREATE TABLE IF NOT EXISTS loanhist"
    " (id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "bloan varchar(20),"//商业贷款
    "gloan varchar(20),"//公积金贷款
    "deadline varchar(20),"//期限
    "brate varchar(20),"//商贷利率
    "grate varchar(20),"//公积金利率
    "cmonth_bx varchar(10),"//月还款本息
    "crate_bx varchar(10),"//本息和本息
    "ctotal_bx varchar(10),"//总利息本息
    "cmonth_bj varchar(10),"//月还款本金
    "crate_bj varchar(10),"//本息和本金
    "ctotal_bj varchar(10),"//总利息本金
    "cmondec_bj varchar(10),"//商贷每月递减额+公积金每月递减额
    "time varchar(30)"//时间
    ")";
    
    [self.fmdb executeUpdate:table_loan_info];
    
    NSString* table_taxes_new_info = @"CREATE TABLE IF NOT EXISTS taxes_new_hist"
    " (id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "square varchar(20),"//房屋面积
    "price varchar(20),"//房屋价格
    "totaltaxes varchar(20),"//总税
    "time varchar(30)"//时间
    ")";
    [self.fmdb executeUpdate:table_taxes_new_info];
    
    NSString* table_taxes_second_info = @"CREATE TABLE IF NOT EXISTS taxes_second_hist"
    " (id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "square varchar(20),"//房屋面积
    "price varchar(20),"//房屋价格
    "totaltaxes varchar(20),"//总税
    "overfive integer(20),"//是否满5年
    "onlyone integer(20),"//是否唯一
    "time varchar(30)"//时间
    ")";
    [self.fmdb executeUpdate:table_taxes_second_info];
    
    NSString* table_qa_info = @"CREATE TABLE IF NOT EXISTS qa_info"
    " (id INTEGER PRIMARY KEY AUTOINCREMENT, "
    "quesid varchar(20),"//问题id
    "ques varchar(200),"//问题描述
    "readflag integer(20),"//是否已阅读
    "replyflag integer(20),"//是否有回答
    "reply varchar(500),"//回答内容
    "time varchar(30)"//时间
    ")";
    [self.fmdb executeUpdate:table_qa_info];
}

//将问答结果保存
- (void)saveToQA:(MsgInfo *)msg{
    //ToolOperation* tool = [ToolOperation shareTool];
    //NSString* timeStr = [tool stringOfCurrentDateTool];
    NSString* sql = [NSString stringWithFormat:@" insert into qa_info(quesid,ques,readflag,replyflag,reply,time) values('%@','%@','%@','%@','%@','%@')",msg.quesID, msg.quesStr, [NSNumber numberWithInt:msg.isRead], [NSNumber numberWithInt:msg.isReply], msg.reply, msg.time];
    
    NSLog(@"sql_saveToQA____%@",sql);
    
    [self.fmdb executeUpdate:sql];
}

//将答案进行更新（当收到第一个答案的时候）
- (BOOL)updateAnswer:(NSString *)answer byID:(NSString *)quesID{
    BOOL successful = [fmdb executeUpdate:@" update qa_info set reply = ?, readflag = ? , replyflag = ? ,time = ? where quesid = ?",answer,[NSNumber numberWithInt:0],[NSNumber numberWithInt:1],[[ToolOperation shareTool]stringOfCurrentDateTool2], quesID];
    return successful;
}

- (BOOL)updateReadFlagbyID:(NSString *)quesID{
    BOOL successful = [fmdb executeUpdate:@" update qa_info set readflag = ? where quesid = ?",[NSNumber numberWithInt:1],quesID];
    return successful;
}

//判断是否已经收到第一个答案（根据 isReply）
- (BOOL)isReplyByQuesID:(NSString *)quesID{
    NSString* sql = [NSString stringWithFormat:@" select * from qa_info where quesid = '%@' and replyflag = '%@'",quesID,[NSNumber numberWithInt:1]];
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [NSMutableArray array];
    
    while ([rs next]) {
        [tmpArr addObject:[rs stringForColumn:@"quesid"]];
    }
    if (tmpArr.count) {
        return YES;
    }
    return NO;
}

//QuestionID数组
-(NSMutableArray*)getAllSectionQuesIDInQAInfo{
    
    NSString* sql = @" select distinct(quesid) from qa_info order by time";
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [NSMutableArray array];
    
    while ([rs next]) {
        
        [tmpArr addObject:[rs stringForColumn:@"quesid"]];
    }
    
    return tmpArr;
}

//根据问题ID获取当前ID下的答案
-(NSMutableArray*)getAllReplysByQuestionID:(NSString*)quesID{
    
    NSString* sql = [NSString stringWithFormat:@" select * from qa_info where quesid = '%@' order by time",quesID];
    
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [NSMutableArray array];
    
    while ([rs next]) {
        
        MsgInfo* msg = [[MsgInfo alloc] init];
        msg.quesID = [rs stringForColumn:@"quesid"];
        msg.quesStr = [rs stringForColumn:@"ques"];
        msg.isRead = [rs intForColumn:@"readflag"];
        msg.isReply = [rs intForColumn:@"replyflag"];
        msg.reply = [rs stringForColumn:@"reply"];
        msg.time = [rs stringForColumn:@"time"];
        [tmpArr addObject:msg];
    }
    
    return tmpArr;
}

//保存房贷历史
-(void)saveToLoanHis:(LoanHisUnit*)loanHis{
    
    ToolOperation* tool = [ToolOperation shareTool];
    NSString* timeStr = [tool stringOfCurrentDateTool];

    //不能插入重复数据
    NSString* selSql = [NSString stringWithFormat:@" select count(*) from loanhist where bloan = '%@' and gloan = '%@' and deadline = '%@' and brate = '%@' and grate = '%@' and cmonth_bx = '%@' and crate_bx = '%@' and ctotal_bx = '%@' and cmonth_bj = '%@' and crate_bj = '%@' and ctotal_bj = '%@' and cmondec_bj = '%@' and time = '%@'", loanHis.bloan, loanHis.gloan, loanHis.deadline, loanHis.brate, loanHis.grate, loanHis.cmonth_bx, loanHis.crate_bx, loanHis.ctotal_bx, loanHis.cmonth_bj, loanHis.crate_bj, loanHis.ctotal_bj, loanHis.cmondec_bj,timeStr];
        
    int retValu = [self.fmdb intForQuery:selSql];
    if ( retValu > 0 ) return; //如果存在纪录，则不插入
    

    NSString* sql = [NSString stringWithFormat:@" insert into loanhist(bloan,gloan,deadline,brate,grate,cmonth_bx,crate_bx,ctotal_bx,cmonth_bj,crate_bj,ctotal_bj,cmondec_bj,time) values('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')",loanHis.bloan, loanHis.gloan, loanHis.deadline, loanHis.brate, loanHis.grate, loanHis.cmonth_bx, loanHis.crate_bx, loanHis.ctotal_bx, loanHis.cmonth_bj, loanHis.crate_bj, loanHis.ctotal_bj, loanHis.cmondec_bj,timeStr];
    
    NSLog(@"sql_saveToHis____%@",sql);
    
    [self.fmdb executeUpdate:sql];
}

//保存新房税费历史
-(void)saveToNewTaxesHis:(TaxesNewHisUnit*)newHis{
    
    ToolOperation* tool = [ToolOperation shareTool];
    NSString* timeStr = [tool stringOfCurrentDateTool];
    
    //不能插入重复数据
    NSString* selSql = [NSString stringWithFormat:@" select count(*) from taxes_new_hist where square = '%@' and price = '%@' and totaltaxes = '%@' and time = '%@'",newHis.square, newHis.price, newHis.totaltaxes,timeStr];
    
    int retValu = [self.fmdb intForQuery:selSql];
    if ( retValu > 0 ) return; //如果存在纪录，则不插入
    
    
    NSString* sql = [NSString stringWithFormat:@" insert into taxes_new_hist(square,price,totaltaxes,time) values('%@','%@','%@','%@')",newHis.square, newHis.price, newHis.totaltaxes,timeStr];
    
    NSLog(@"sql_saveToHis____%@",sql);
    
    [self.fmdb executeUpdate:sql];
}

//保存二手房税费历史
-(void)saveToSecondTaxesHis:(TaxesSecondHisUnit*)secondHis{
    
    ToolOperation* tool = [ToolOperation shareTool];
    NSString* timeStr = [tool stringOfCurrentDateTool];
    
    //不能插入重复数据
    NSString* selSql = [NSString stringWithFormat:@" select count(*) from taxes_second_hist where square = '%@' and price = '%@' and totaltaxes = '%@' and overfive = '%@' and onlyone = '%@' and time = '%@'",secondHis.square, secondHis.price, secondHis.totaltaxes, [NSNumber numberWithBool:secondHis.overfive], [NSNumber numberWithBool:secondHis.onlyone],timeStr];
    
    int retValu = [self.fmdb intForQuery:selSql];
    if ( retValu > 0 ) return; //如果存在纪录，则不插入
    
    
    NSString* sql = [NSString stringWithFormat:@" insert into taxes_second_hist(square,price,totaltaxes,overfive,onlyone,time) values('%@','%@','%@','%@','%@','%@')",secondHis.square, secondHis.price, secondHis.totaltaxes, [NSNumber numberWithBool:secondHis.overfive], [NSNumber numberWithBool:secondHis.onlyone],timeStr];
    
    NSLog(@"sql_saveToHis____%@",sql);
    
    [self.fmdb executeUpdate:sql];
}

//根据日期获取当前日期下的数组
-(NSMutableArray*)getAllHistoryByTimeInLoanHist:(NSString*)timeStr{
    
    NSString* sql = [NSString stringWithFormat:@" select * from  loanhist where time = '%@' order by id DESC",timeStr];
    
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [[NSMutableArray alloc] initWithCapacity:10];
    
    while ([rs next]) {
        
        LoanHisUnit* hut = [[LoanHisUnit alloc] init];
        
        hut.bloan = [rs stringForColumn:@"bloan"];
        hut.gloan = [rs stringForColumn:@"gloan"];
        hut.deadline = [rs stringForColumn:@"deadline"];
        hut.brate = [rs stringForColumn:@"brate"];
        hut.grate = [rs stringForColumn:@"grate"];
        hut.cmonth_bx = [rs stringForColumn:@"cmonth_bx"];
        hut.crate_bx = [rs stringForColumn:@"crate_bx"];
        hut.ctotal_bx = [rs stringForColumn:@"ctotal_bx"];
        hut.cmonth_bj = [rs stringForColumn:@"cmonth_bj"];
        hut.crate_bj = [rs stringForColumn:@"crate_bj"];
        hut.ctotal_bj = [rs stringForColumn:@"ctotal_bj"];
        hut.cmondec_bj = [rs stringForColumn:@"cmondec_bj"];
        hut.time = [rs stringForColumn:@"time"];
        
        [tmpArr addObject:hut];
    }
    
    return tmpArr;
}


//根据日期获取当前日期下的数组
-(NSMutableArray*)getAllHistoryByTimeInNewTaxesHist:(NSString*)timeStr{
    
    NSString* sql = [NSString stringWithFormat:@" select * from  taxes_new_hist where time = '%@' order by id DESC",timeStr];
    
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [[NSMutableArray alloc] initWithCapacity:10];
    
    while ([rs next]) {
        
        TaxesNewHisUnit* hut = [[TaxesNewHisUnit alloc] init];
        
        hut.price = [rs stringForColumn:@"price"];
        hut.square = [rs stringForColumn:@"square"];
        hut.totaltaxes = [rs stringForColumn:@"totaltaxes"];
        hut.time = [rs stringForColumn:@"time"];

        [tmpArr addObject:hut];
    }
    
    return tmpArr;
}

//根据日期获取当前日期下的数组
-(NSMutableArray*)getAllHistoryByTimeInSecondTaxesHist:(NSString*)timeStr{
    
    NSString* sql = [NSString stringWithFormat:@" select * from  taxes_second_hist where time = '%@' order by id DESC",timeStr];
    
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [[NSMutableArray alloc] initWithCapacity:10];
    
    while ([rs next]) {
        
        TaxesSecondHisUnit* hut = [[TaxesSecondHisUnit alloc] init];
        
        hut.price = [rs stringForColumn:@"price"];
        hut.square = [rs stringForColumn:@"square"];
        hut.totaltaxes = [rs stringForColumn:@"totaltaxes"];
        hut.overfive = [rs boolForColumn:@"overfive"];
        hut.onlyone = [rs boolForColumn:@"onlyone"];
        hut.time = [rs stringForColumn:@"time"];
        
        [tmpArr addObject:hut];
    }
    
    return tmpArr;
}

//Time section数组
-(NSMutableArray*)getAllSectionTimeInLoanHist{
        
    NSString* sql = @" select distinct(time) from  loanhist order by id DESC";
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [[NSMutableArray alloc] initWithCapacity:10];
    
    while ([rs next]) {
        
        [tmpArr addObject:[rs stringForColumn:@"time"]];
    }
    
    return tmpArr;
}

//Time section数组
-(NSMutableArray*)getAllSectionTimeInNewTaxesHist{
    
    NSString* sql = @" select distinct(time) from taxes_new_hist order by id DESC";
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [[NSMutableArray alloc] initWithCapacity:10];
    
    while ([rs next]) {
        
        [tmpArr addObject:[rs stringForColumn:@"time"]];
    }
    
    return tmpArr;
}

//Time section数组
-(NSMutableArray*)getAllSectionTimeInSecondTaxesHist{
    
    NSString* sql = @" select distinct(time) from taxes_second_hist order by id DESC";
    FMResultSet* rs = [fmdb executeQuery:sql];
    
    NSMutableArray* tmpArr = [[NSMutableArray alloc] initWithCapacity:10];
    
    while ([rs next]) {
        
        [tmpArr addObject:[rs stringForColumn:@"time"]];
    }
    
    return tmpArr;
}

//获取所有数据字典
-(NSMutableDictionary*)getAllDicInLoanHist{
   
    NSMutableArray* timArr = [self getAllSectionTimeInLoanHist];
    
    NSMutableDictionary* dict = [[NSMutableDictionary alloc] initWithCapacity:10];
    
    for (NSString* timeStr in timArr) {
      
        [dict setObject:[self getAllHistoryByTimeInLoanHist:timeStr] forKey:timeStr];
    }
    
   return dict;
}

//获取所有数据字典
-(NSMutableDictionary*)getAllDicInNewTaxesHist{
    
    NSMutableArray* timArr = [self getAllSectionTimeInNewTaxesHist];
    
    NSMutableDictionary* dict = [[NSMutableDictionary alloc] initWithCapacity:10];
    
    for (NSString* timeStr in timArr) {
        
        [dict setObject:[self getAllHistoryByTimeInNewTaxesHist:timeStr] forKey:timeStr];
    }
    
    return dict;
}
//获取所有数据字典
-(NSMutableDictionary*)getAllDicInSecondTaxesHist{
    
    NSMutableArray* timArr = [self getAllSectionTimeInSecondTaxesHist];
    
    NSMutableDictionary* dict = [[NSMutableDictionary alloc] initWithCapacity:10];
    
    for (NSString* timeStr in timArr) {
        
        [dict setObject:[self getAllHistoryByTimeInSecondTaxesHist:timeStr] forKey:timeStr];
    }
    
    return dict;
}
@end